Imports System.Data.SqlClient
Public Class FrmBCTongHop

    Private Sub FrmBCTongHop_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        For i As Integer = Now.Year - 10 To Now.Year + 9
            cbxNamQuy.Items.Add(i)
            cbxNamDau.Items.Add(i)
            cbxNamCuoi.Items.Add(i)
            cbxNam.Items.Add(i)
        Next
        cbxNamCuoi.Text = Now.Year.ToString()
        cbxNamDau.Text = Now.Year.ToString()
        cbxNamQuy.Text = Now.Year.ToString()
        cbxThangCuoi.Text = Now.Month.ToString()
        cbxThangDau.Text = Now.Month.ToString()
        cbxNam.Text = Now.Year.ToString()
        cbxQuy.Text = Math.Ceiling(Now.Month / 3).ToString()
    End Sub

    Private Sub btnIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIn.Click
        Dim NgayBatDau As Date
        Dim NgayKetThuc As Date
        If radNgay.Checked Then
            NgayBatDau = dtpFrom.Value
            NgayKetThuc = dtpTo.Value
        ElseIf radThang.Checked Then
            NgayBatDau = New Date(CInt(cbxNamDau.Text), CInt(cbxThangDau.Text), 1)
            If cbxThangCuoi.Text <> "" And cbxNamCuoi.Text <> "" Then
                NgayKetThuc = New Date(CInt(cbxNamCuoi.Text), CInt(cbxThangCuoi.Text), Date.DaysInMonth(CInt(cbxNamCuoi.Text), CInt(cbxThangCuoi.Text)))
            Else
                NgayKetThuc = New Date(CInt(cbxNamDau.Text), CInt(cbxThangDau.Text), Date.DaysInMonth(CInt(cbxNamDau.Text), CInt(cbxThangDau.Text)))
            End If
        ElseIf radQuy.Checked Then
            Select Case cbxQuy.Text
                Case "1"
                    NgayBatDau = New Date(CInt(cbxNamQuy.Text), 1, 1)
                    NgayKetThuc = New Date(CInt(cbxNamQuy.Text), 3, 31)
                Case "2"
                    NgayBatDau = New Date(CInt(cbxNamQuy.Text), 4, 1)
                    NgayKetThuc = New Date(CInt(cbxNamQuy.Text), 6, 30)
                Case "3"
                    NgayBatDau = New Date(CInt(cbxNamQuy.Text), 7, 1)
                    NgayKetThuc = New Date(CInt(cbxNamQuy.Text), 9, 30)
                Case "4"
                    NgayBatDau = New Date(CInt(cbxNamQuy.Text), 10, 1)
                    NgayKetThuc = New Date(CInt(cbxNamQuy.Text), 12, 31)
            End Select
        Else
            NgayBatDau = New Date(CInt(cbxNam.Text), 1, 1)
            NgayKetThuc = New Date(CInt(cbxNam.Text), 12, 31)
        End If
        XuatExcel(NgayBatDau, NgayKetThuc)
    End Sub

    Private Sub btnDong_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDong.Click
        Me.Close()
    End Sub

    Function LayDanhSachButToan(ByVal MaHH As String, ByVal NgayBatDau As Date, ByVal NgayKetThuc As Date) As DataTable
        Dim strSql As String = "Select "
        strSql &= " ct.MaPhieuCT, "
        strSql &= " sc.LoaiCT, "
        strSql &= " ct.SoLuong, "
        strSql &= " case "
        strSql &= " when ct.USD = 1 then sc.TyGia * ct.SoLuong * ct.DonGia "
        strSql &= "	else ct.SoLuong * ct.DonGia "
        strSql &= " end as TienHang, "
        strSql &= " ct.ThueNK, "
        strSql &= " ct.TonKho, "
        strSql &= " ct.GiaTri, "
        strSql &= " ct.GiaVon "
        strSql &= " from"
        strSql &= " CTSoCai ct"
        strSql &= " inner join SoCai sc on ct.MaPhieu = sc.MaPhieu"
        strSql &= " where "
        strSql &= " sc.NgayCT >= convert(DATETIME, @From, 103) "
        strSql &= " and sc.NgayCT <= convert(DATETIME, @To, 103) "
        strSql &= " and ct.MaHH = @MaHH "
        strSql &= " order by sc.NgayCT ASC, sc.LoaiCT"
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.SelectCommand.Parameters.AddWithValue("@From", NgayBatDau.ToString("dd/MM/yyyy"))
            myAdapter.SelectCommand.Parameters.AddWithValue("@To", NgayKetThuc.ToString("dd/MM/yyyy"))
            myAdapter.SelectCommand.Parameters.AddWithValue("@MaHH", MaHH)
            myAdapter.Fill(myTable)
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return myTable
    End Function

    Function LayButToanTruocDo(ByVal MaHH As String, ByVal NgayBatDau As Date) As DataTable
        Dim strSql As String = "Select Top 1 "
        strSql &= " ct.MaPhieuCT, "
        strSql &= " sc.LoaiCT, "
        strSql &= " ct.SoLuong, "
        strSql &= " case "
        strSql &= " when ct.USD = 1 then sc.TyGia * ct.SoLuong * ct.DonGia "
        strSql &= "	else ct.SoLuong * ct.DonGia "
        strSql &= " end as TienHang, "
        strSql &= " ct.ThueNK, "
        strSql &= " ct.TonKho, "
        strSql &= " ct.GiaTri, "
        strSql &= " ct.GiaVon "
        strSql &= " from"
        strSql &= " CTSoCai ct"
        strSql &= " inner join SoCai sc on ct.MaPhieu = sc.MaPhieu"
        strSql &= " where "
        strSql &= " sc.NgayCT < convert(DATETIME, @From, 103) "
        strSql &= " and datepart(Month, sc.NgayCT) = @Thang "
        strSql &= " and datePart(Year, sc.NgayCT) = @Nam "
        strSql &= " and ct.MaHH = @MaHH "
        strSql &= " order by sc.NgayCT DESC, sc.LoaiCT"
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.SelectCommand.Parameters.AddWithValue("@From", NgayBatDau.ToString("dd/MM/yyyy"))
            myAdapter.SelectCommand.Parameters.AddWithValue("@MaHH", MaHH)
            myAdapter.SelectCommand.Parameters.AddWithValue("@Thang", dtpFrom.Value.Month)
            myAdapter.SelectCommand.Parameters.AddWithValue("@Nam", dtpFrom.Value.Year)
            myAdapter.Fill(myTable)
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return myTable
    End Function

    Private Sub radNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radNgay.CheckedChanged
        If radNgay.Checked Then
            dtpFrom.Enabled = True
            dtpTo.Enabled = True
        Else
            dtpFrom.Enabled = False
            dtpTo.Enabled = False
        End If
    End Sub

    Private Sub radThang_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radThang.CheckedChanged
        If radThang.Checked Then
            cbxThangDau.Enabled = True
            cbxNamDau.Enabled = True
            cbxThangCuoi.Enabled = True
            cbxNamCuoi.Enabled = True
        Else
            cbxThangDau.Enabled = False
            cbxNamDau.Enabled = False
            cbxThangCuoi.Enabled = False
            cbxNamCuoi.Enabled = False
        End If
    End Sub

    Private Sub radQuy_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radQuy.CheckedChanged
        If radQuy.Checked Then
            cbxQuy.Enabled = True
            cbxNamQuy.Enabled = True
        Else
            cbxQuy.Enabled = False
            cbxNamQuy.Enabled = False
        End If
    End Sub

    Private Sub radNam_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radNam.CheckedChanged
        If radNam.Checked Then
            cbxNam.Enabled = True
        Else
            cbxNam.Enabled = False
        End If
    End Sub

    Sub XuatExcel(ByVal NgayBatDau As Date, ByVal NgayKetThuc As Date)
        Dim strPath = Application.StartupPath & "\MauBaoCao\BCTongHop.vthh"
        Dim strSPath = Application.StartupPath & "\DuLieuTam\BaoCaoTongHop_" & Now.Ticks & ".xls"
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Try

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False

            xlWorkBook = xlApp.Workbooks.Open(strPath)
            xlWorkBook.SaveCopyAs(strSPath)
            xlWorkBook.Close()
            xlWorkBook = xlApp.Workbooks.Open(strSPath)
            xlWorkSheet = xlWorkBook.Worksheets("SHN")
            'edit the cell with new value
            xlWorkSheet.Cells(1, 1).value = ThietLap("CFG01")
            xlWorkSheet.Cells(2, 1).value = xlWorkSheet.Cells(2, 1).value.ToString.Replace("{DiaChi}", ThietLap("CFG02"))
            xlWorkSheet.Cells(3, 1).value = xlWorkSheet.Cells(3, 1).value.ToString.Replace("{MST}", ThietLap("CFG04"))

            Dim strNgay As String = xlWorkSheet.Cells(6, 1).value.ToString.Replace("{TuNgay}", NgayBatDau.ToString("dd/MM/yyyy"))
            strNgay = strNgay.Replace("{DenNgay}", NgayKetThuc.ToString("dd/MM/yyyy"))
            xlWorkSheet.Cells(6, 1).value = strNgay

            Dim strNgayXuat As String = xlWorkSheet.Cells(12, 10).value.ToString.Replace("{NgayCT}", Now.Day)
            strNgayXuat = strNgayXuat.Replace("{ThangCT}", Now.Month)
            strNgayXuat = strNgayXuat.Replace("{NamCT}", Now.Year)
            xlWorkSheet.Cells(12, 10).value = strNgayXuat

            xlWorkSheet.Cells(18, 2).value = ThietLap("CFG09")
            xlWorkSheet.Cells(18, 6).value = ThietLap("CFG08")
            xlWorkSheet.Cells(18, 10).value = ThietLap("CFG06")

            Dim tblHangHoa As DataTable = TruyVanDanhSach("Select hh.MaHH, hh.TenHH, dv.TenDV From HangHoa hh left join DonVi dv on hh.MaDV = dv.MaDV")
            Dim i As Integer
            For i = 0 To tblHangHoa.Rows.Count - 1
                Dim row As DataRow = tblHangHoa.Rows(i)
                xlWorkSheet.Cells(10 + i, 1).value = row(0)
                xlWorkSheet.Cells(10 + i, 2).value = row(1)
                xlWorkSheet.Cells(10 + i, 3).value = row(2)

                Dim sldk As Double = 0
                Dim gtdk As Double = 0
                Dim psnsl As Double = 0
                Dim psngt As Double = 0
                Dim psxgt As Double = 0
                Dim psxsl As Double = 0
                Dim gtck As Double = 0
                Dim slck As Double = 0
                Dim tblButToan As DataTable = LayDanhSachButToan(row(0), NgayBatDau, NgayKetThuc)
                If tblButToan.Rows.Count > 0 Then
                    sldk += tblButToan.Rows(0)(5)
                    gtdk += tblButToan.Rows(0)(6)
                    For j As Integer = 0 To tblButToan.Rows.Count - 1
                        Dim rowbt As DataRow = tblButToan.Rows(j)
                        If rowbt(1) = "PNK" Then
                            psnsl += rowbt(2)
                            psngt += rowbt(3) + rowbt(4)
                            If j = tblButToan.Rows.Count - 1 Then
                                slck += rowbt(5) + rowbt(2)
                                gtck += rowbt(6) + rowbt(3) + rowbt(4)
                            End If
                        Else
                            psxsl += rowbt(2)
                            psxgt += rowbt(2) * rowbt(7)
                            If j = tblButToan.Rows.Count - 1 Then
                                slck += rowbt(5) - rowbt(2)
                                gtck += rowbt(6) - (rowbt(2) * rowbt(7))
                            End If
                        End If
                    Next
                Else
                    tblButToan = LayButToanTruocDo(row(0), NgayBatDau)
                    If tblButToan.Rows.Count > 0 Then
                        sldk += tblButToan.Rows(0)(5) + tblButToan.Rows(0)(2)
                        gtdk += tblButToan.Rows(0)(6) + tblButToan.Rows(0)(3) + tblButToan.Rows(0)(4)
                        slck += tblButToan.Rows(0)(5) + tblButToan.Rows(0)(2)
                        gtck += tblButToan.Rows(0)(6) + tblButToan.Rows(0)(3) + tblButToan.Rows(0)(4)
                    Else
                        tblButToan = TruyVanDanhSach("Select SLTonDK, GTTonDK, GiaVonDK from CTHangHoa where MaHH = '" & row(0) & "' and Thang = " & dtpFrom.Value.Month & " and Nam = " & dtpFrom.Value.Year)
                        If tblButToan.Rows.Count > 0 Then
                            sldk += tblButToan.Rows(0)(0)
                            gtdk += tblButToan.Rows(0)(1)
                            slck += tblButToan.Rows(0)(0)
                            gtck += tblButToan.Rows(0)(1)
                        End If
                    End If
                End If
                xlWorkSheet.Cells(10 + i, 4).value = sldk
                xlWorkSheet.Cells(10 + i, 5).value = gtdk
                xlWorkSheet.Cells(10 + i, 6).value = psnsl
                xlWorkSheet.Cells(10 + i, 7).value = psngt
                xlWorkSheet.Cells(10 + i, 8).value = psxsl
                xlWorkSheet.Cells(10 + i, 9).value = psxgt
                xlWorkSheet.Cells(10 + i, 10).value = slck
                xlWorkSheet.Cells(10 + i, 11).value = gtck
                If i <> tblHangHoa.Rows.Count - 1 Then
                    xlWorkSheet.Rows(11 + i).Select()
                    xlApp.Selection.Insert(Excel.XlInsertShiftDirection.xlShiftDown)
                    xlWorkSheet.Rows(10).Select()
                    xlApp.Selection.Copy()
                    xlWorkSheet.Rows(11 + i).Select()
                    xlApp.ActiveSheet.Paste()
                End If
            Next
            xlWorkSheet.Cells(11 + i - 1, 4).value = "=Sum(D10:D" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 5).value = "=Sum(E10:E" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 6).value = "=Sum(F10:F" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 7).value = "=Sum(G10:G" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 8).value = "=Sum(H10:H" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 9).value = "=Sum(I10:I" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 10).value = "=Sum(J10:J" & (11 + i - 2) & ")"
            xlWorkSheet.Cells(11 + i - 1, 11).value = "=Sum(K10:K" & (11 + i - 2) & ")"
            xlWorkBook.Save()
            xlWorkSheet.Cells(100, 100).Select()
            xlApp.Selection.Copy()
            xlWorkSheet.Cells(1, 1).Select()
            xlApp.Visible = True
            xlWorkBook.Activate()
            xlApp.DisplayAlerts = True

        Catch ex As Exception

        End Try
    End Sub

    Public Function TruyVanDanhSach(ByVal strSql As String) As DataTable
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.Fill(myTable)
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return myTable
    End Function

End Class